SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMSACA_MENS_Q02]
/*-------------------------------------------------------*/
/*---Empresa              : OFISIS S.A.                --*/
/*---Cliente              : M,F,R,P-T&L-V              --*/
/*---Sistema              : Tesoreria                  --*/
/*---M¸dulo               : Empresa                    --*/
/*---Programa             : Consulta de saldos         --*/
/*---                       Movimiento Caja 	       --*/
/*---Script               : TMSACA_M.SQL	       --*/
/*---Nombre SP            : SP_TMSACA_MENS_Q02        --*/
/*---Desarrollado por     : Denis Villacres	       --*/
/*---Fecha Creaci¸n       : 19/07/1999                 --*/
/*---Base Datos           : Microsoft Sql Server       --*/
/*---Versi¸n              : 7.0                        --*/
/*---Invoca a SP          :                            --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Modificado 1 por     :                            --*/
/*---Fecha Modificaci¸n   :                            --*/ 
/*---Detalle Modificaci¸n :                            --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Comentarios                                       --*/
/*---                                                  --*/
/*---                                                  --*/
/*-------------------------------------------------------*/

/*------------------------------------------------------ */
/* Drop Proc SP_TMSACA_MENS_Q02			 	 */
/* Grant All On SP_TMSACA_MENS_Q02 To Public	 	 */
/* SP_TMSACA_MENS_Q02 '10', null ,'1999','07'   	 */
/*-------------------------------------------------------*/
@ISCO_USUA   TD_VC_008_USUA,
@ISCO_EMPR   TD_VC_002,
@ISCO_CAJA   TD_VC_008,
@ISAA_CAJA   TD_VC_004,
@ISMM_CAJA   TD_VC_002, 
@ISTI_REPO   TD_VC_001
	As
Create Table #TWSACA_MENS_Q02_1
(
 CO_EMPR       	Varchar(2) Null,
 DE_NOMB       	Varchar(50) Null,	
 CO_CAJA       	Varchar(8) Null,
 DE_CAJA       	Varchar(50) Null,	
 IM_SALD_INIC  	Numeric(16,4) Null,
 IM_INGR 	Numeric(16,4) Null,
 IM_EGRE 	Numeric(16,4) Null,
 IM_TOTA 	Numeric(16,4) Null,
 CO_MONE 	Varchar(3) Null,
 MX_FEOP	Datetime Null, 
 MX_FEOP_CHAR	Varchar(10) Null
)

/*************************************
 Declaraci©n de variables de trabajo
**************************************/
Declare
/**************************************
 Declaraci©n de las variables del cursor
***************************************/
@CSCO_EMPR	TD_VC_002 ,
@CSDE_NOMB    	TD_VC_050 ,
@CSCO_CAJA	TD_VC_008 ,
@CSDE_CAJA  	TD_VC_050 ,
@CNIM_SALD_INIC	TD_NU_016_004 ,
@CNIM_INGR     	TD_NU_016_004 ,
@CNIM_EGRE     	TD_NU_016_004,
@CSCO_MONE	TD_VC_003,
@CDMX_FEOP	TD_DT_001,
@CDMX_FEOP_CHAR	TD_VC_010,

/*Variables*/
@VNIM_INGR     	TD_NU_016_004 ,
@VSCO_EMPR	TD_VC_002 ,
@VNIM_EGRE     	TD_NU_016_004 ,
@VNIM_SALD_INIC TD_NU_016_004 ,
@VNIM_SALD_ANTE TD_NU_016_004 ,
@VNIM_TOTA	TD_NU_016_004 ,
@VNNU_CAJA      TD_IN_001 ,
@VSAA_CAJA	TD_VC_004 ,
@VSMM_CAJA	TD_VC_002

/**************************************
Declaraci©n de cursor
***************************************/
Declare CU_TMSACA_MENS_I01 CURSOR
For

 Select	 T1.CO_EMPR, T3.DE_NOMB, T1.CO_CAJA, T2.DE_CAJA, Isnull(T1.IM_SALD_INIC,0), 
	 Isnull(T1.IM_INGR,0), 
	 Isnull(T1.IM_EGRE,0), T2.CO_MONE,
	 (Select  Max(T4.FE_OPER)
	  From 	  TCMOVI_CAJA T4
	  Where	  T1.CO_EMPR = T4.CO_EMPR
		  And T1.CO_CAJA = T4.CO_CAJA),

	 (Select  Max(Convert(Varchar(10), T4.FE_OPER, 103))
	  From 	  TCMOVI_CAJA T4
	  Where	  T1.CO_EMPR = T4.CO_EMPR
		  And T1.CO_CAJA = T4.CO_CAJA)

 From 	 TMSACA_MENS T1, TMCAJA T2, TMEMPR T3
 Where   T1.CO_EMPR LIKE ISNULL( Ltrim( @ISCO_EMPR ), '%')
  	 And T1.CO_CAJA LIKE ISNULL( Ltrim(@ISCO_CAJA ), '%')
  	 And T1.AA_CAJA = @ISAA_CAJA 

  	 And T1.MM_CAJA = @ISMM_CAJA
  	 And T1.CO_EMPR = T2.CO_EMPR
  	 And T1.CO_CAJA = T2.CO_CAJA
  	 And T1.CO_EMPR = T3.CO_EMPR
  	 And  Exists ( 	Select 	 * 
			From  	 TTUSER_CAJA T4
			Where	 T1.CO_EMPR = T4.CO_EMPR
			    	 And T1.CO_CAJA  = T4.CO_CAJA
			    	 And T4.CO_USUA = @ISCO_USUA
			    	 And T4.ST_REPO = 'S' )


Open CU_TMSACA_MENS_I01
Fetch CU_TMSACA_MENS_I01 Into @CSCO_EMPR, @CSDE_NOMB, @CSCO_CAJA, @CSDE_CAJA, @CNIM_SALD_INIC, 
		              @CNIM_INGR, @CNIM_EGRE, @CSCO_MONE, @CDMX_FEOP, @CDMX_FEOP_CHAR

WHILE (@@FETCH_STATUS = 0)
Begin
 If (@@FETCH_STATUS < 0)
   Begin
    RAISERROR 20001 'FIN DE ARCHIVO'
    Close CU_TMSACA_MENS_I01
    Deallocate CU_TMSACA_MENS_I01
    Return
   End
 /*************************************
 Hallar el saldo del mes anterior
 *************************************/
 Select @VNIM_SALD_ANTE = 0
 Select @VNIM_SALD_INIC = 0
 Select @VNIM_TOTA = 0	   

 If @ISMM_CAJA = '01'
  Begin 
     Select @VSMM_CAJA = '12'
     Select @VSAA_CAJA = @ISAA_CAJA - 1
  End
 else
  Begin
     Select @VSMM_CAJA = @ISMM_CAJA - 1
     select @VSAA_CAJA = @ISAA_CAJA
  End

 If Len(@VSMM_CAJA)=1
    Select @VSMM_CAJA = '0'+ @VSMM_CAJA

 /*************************************
 Nomero de Registros de periodo anterior
 **************************************/

 Select @VNNU_CAJA = Count(*)
 From   TMSACA_MENS
 where  CO_EMPR = @CSCO_EMPR
 	And CO_CAJA = @CSCO_CAJA
 	And AA_CAJA = @VSAA_CAJA
 	And MM_CAJA = @VSMM_CAJA   

 If @VNNU_CAJA > 0 
  Begin
     Select   @VNIM_SALD_ANTE = Isnull( IM_SALD_INIC, 0)+ 
		Isnull( 
		( 
		Select 	Sum(Isnull(IM_MOVI,0))
		From 	TCMOVI_CAJA 
		Where   CO_EMPR = @ISCO_EMPR
  	 		And CO_CAJA = @CSCO_CAJA
   	      		And AA_CAJA = @VSAA_CAJA
   	      		And MM_CAJA = @VSMM_CAJA
			And TI_MOVI = 'I'
			And TI_SITU = 'CAR'
		), 0), 
	      @VNIM_INGR = Isnull(IM_INGR,0), 
	      @VNIM_EGRE = Isnull(IM_EGRE,0)
     From     TMSACA_MENS	
     Where    CO_EMPR = @CSCO_EMPR
   	      And CO_CAJA = @CSCO_CAJA
   	      And AA_CAJA = @VSAA_CAJA
   	      And MM_CAJA = @VSMM_CAJA

     Select @VNIM_SALD_INIC = @VNIM_SALD_ANTE + @VNIM_INGR - @VNIM_EGRE
     Select @VNIM_TOTA = @VNIM_SALD_INIC + @CNIM_INGR - @CNIM_EGRE 
  End  
 Else
  Begin		
     Select @VNIM_SALD_INIC = @CNIM_SALD_INIC 
     Select @VNIM_TOTA = @VNIM_SALD_INIC + @CNIM_INGR - @CNIM_EGRE      
  End

 /*************************************/
 /*Asigna valores a la tabla temporal*/
 /*************************************/
 Insert Into #TWSACA_MENS_Q02_1(CO_EMPR, DE_NOMB, CO_CAJA, DE_CAJA, IM_SALD_INIC, 
		                IM_INGR, IM_EGRE, IM_TOTA, CO_MONE, MX_FEOP, MX_FEOP_CHAR)
 Values (@CSCO_EMPR, @CSDE_NOMB, @CSCO_CAJA, @CSDE_CAJA, @VNIM_SALD_INIC, @CNIM_INGR, 
	@CNIM_EGRE, @VNIM_TOTA, @CSCO_MONE, @CDMX_FEOP, @CDMX_FEOP_CHAR)
 
 Fetch CU_TMSACA_MENS_I01 
 Into  @CSCO_EMPR, @CSDE_NOMB, @CSCO_CAJA, @CSDE_CAJA, @CNIM_SALD_INIC, @CNIM_INGR, 
       @CNIM_EGRE, @CSCO_MONE, @CDMX_FEOP, @CDMX_FEOP_CHAR

END
Close CU_TMSACA_MENS_I01
Deallocate CU_TMSACA_MENS_I01


UPDATE #TWSACA_MENS_Q02_1
SET IM_INGR = ISNULL(IM_INGR, 0) + Isnull(
		(
		Select 	Sum(Isnull(T1.IM_MOVI,0))
		From 	TCMOVI_CAJA T1
		Where   T1.CO_EMPR = T2.CO_EMPR
  	 		And T1.CO_CAJA = T2.CO_CAJA
   	      		And T1.AA_CAJA = @ISAA_CAJA
   	      		And T1.MM_CAJA = @ISMM_CAJA
			And T1.TI_MOVI = 'I'
			And T1.TI_SITU = 'CAR'), 0), 
    IM_TOTA = Isnull(IM_SALD_INIC, 0) + 
		Isnull(IM_INGR, 0) + Isnull(
		(
		Select 	Sum(Isnull(T1.IM_MOVI,0))
		From 	TCMOVI_CAJA T1
		Where   T1.CO_EMPR = T2.CO_EMPR
  	 		And T1.CO_CAJA = T2.CO_CAJA
   	      		And T1.AA_CAJA = @ISAA_CAJA
   	      		And T1.MM_CAJA = @ISMM_CAJA
			And T1.TI_MOVI = 'I'
			And T1.TI_SITU = 'CAR'), 0)
		- Isnull(IM_EGRE, 0)
FROM #TWSACA_MENS_Q02_1 T2



/*Select Final para el Reporte*/
-- Depende del Tipo de Reporte (@ISTI_REPO).

If @ISTI_REPO = 'D'

	Select 	  * 

	From 	  #TWSACA_MENS_Q02_1

	Where     IM_TOTA < 0
	Order By  CO_EMPR, CO_MONE, MX_FEOP, CO_CAJA

If @ISTI_REPO = 'A'
	Select 	  * 
	From 	  #TWSACA_MENS_Q02_1
	Where     IM_TOTA > 0
	Order By  CO_EMPR, CO_MONE, MX_FEOP, CO_CAJA

If @ISTI_REPO = 'T'
	Select 	  * 
	From 	  #TWSACA_MENS_Q02_1
	Where     IM_TOTA != 0
	Order By  CO_EMPR, CO_MONE, MX_FEOP, CO_CAJA

Return
/*------------------------------------- FIN -------------------------------------*/


GO
